package com.kakarote.crm9.utils;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * sql处理工具类
 * @author xjl
 * @date 2021/3/17 8:59
 */
public class SqlUtil {

    private List<Object> params = new ArrayList<>();

    /**
     * 获取sql待传入的参数，请先调用 convert 方法后再调用此方法
     * @return
     */
    public List<Object> getParams() {
        return this.params;
    }

    /**
     * sql转换，自动解析 <where> <if test="xxx != xxx">xxx</if> </where>
     * @param sql 待转换sql
     * @param obj sql参数对象
     * @return 返回可使用的sql 例：select * from t_o_opinions where  username like concat('%', ?, '%') and content = ? and `state` = ?
     */
    public String convert(String sql, Object obj) throws Exception {
        int start = sql.indexOf("<where>");
        int end = sql.indexOf("</where>");
        String temp = sql.substring(start + 7, end);
        Pattern pattern = Pattern.compile("<if test=\".+\">.+</if>");
        Matcher matcher = pattern.matcher(temp);

        StringBuilder builder = new StringBuilder();

        while (matcher.find()) {
            String group = matcher.group();
            // <if ....>...</if>
//            System.out.println(group);
            String ifStr = group.substring(group.indexOf("test=\"") + 6, group.lastIndexOf("\">"));

//            System.out.println(ifStr);

            decode(group, ifStr, builder, obj);
        }
        String prefix = sql.substring(0, sql.indexOf("<where>"));
        String other = sql.substring(sql.lastIndexOf("</where>") + 8);
        String s = builder.toString();
        if (s.length() > 0) {
            if (s.trim().substring(0, 4).equalsIgnoreCase("and ")) s = s.substring(4);
            if (s.trim().substring(0, 3).equalsIgnoreCase("or ")) s = s.substring(3);
            String suffix = " where " + s;
//            System.out.println(prefix + suffix);
//            System.out.println(params);
            return prefix + suffix + other;
        }
//        System.out.println(params);
        return prefix + other;
    }

    /**
     * 解析判断部分sql
     * @param group
     * @param ifStr
     * @param builder
     * @param obj
     * @throws Exception
     */
    private void decode(String group, String ifStr, StringBuilder builder, Object obj) throws Exception {
        if (ifStr.contains("and")) {
            String[] ands = ifStr.split("and");
            // 当前条件是否通过
            int flag = 0;
            for (String and : ands) {
                // 判断 不等于
                if (and.contains("!=")) {
                    String[] split = and.split("!=");
                    String key = split[0].trim();
                    String val = split[1].trim();
                    if (obj instanceof Map) {
                        Map map = (Map) obj;
                        if (val.equals("null")) {
                            if (map.get(key) != null) flag++;
                        }
                        if (val.equals("''")) {
                            if (map.get(key) != null && !(map.get(key).toString().length() == 0)) flag++;
                        }
                    } else {
                        Field field = obj.getClass().getDeclaredField(key);
                        field.setAccessible(true);
                        if (val.equals("null")) {
                            if (field.get(obj) != null) flag++;
                        }
                        if (val.equals("''")) {
    //                            if (!field.get(obj).toString().equals("''")) flag++;
                            if (field.get(obj) != null && !(field.get(obj).toString().length() == 0)) flag++;
                        }
                    }
                }
                // 判断 等于
                if (and.contains("==")) {
                    String[] split = and.split("==");
                    String key = split[0].trim();
                    String val = split[1].trim();
                    if (obj instanceof Map) {
                        Map map = (Map) obj;
                        if (val.equals("null")) {
                            if (map.get(key) != null) flag++;
                        }
                        if (val.equals("''")) {
                            if (map.get(key) != null && map.get(key).toString().length() == 0) flag++;
                        }
                    } else {
                        Field field = obj.getClass().getDeclaredField(key);
                        field.setAccessible(true);
                        if (val.equals("null")) {
                            if (field.get(obj) == null) flag++;
                        }
                        if (val.equals("''")) {
                            if (field.get(obj) != null && field.get(obj).toString().length() == 0) flag++;
                        }
                    }
                }
            }

            // 条件成立
            if (flag == ands.length) {
                // 条件中的内容 例： and opinion_id = #{opinionId}
                getSqlWillCompilePart(builder, getSqlPart(group), obj);
            }
        }

        if (!ifStr.contains("and")) {
            if (ifStr.contains("!=")) {
                String[] split = ifStr.split("!=");
                String key = split[0].trim();
                String val = split[1].trim();
                if (obj instanceof Map) {
                    Map map = (Map) obj;
                    if (val.equals("null")) {
                        if (map.get(key) != null) {
                            // 条件中的内容 例： and opinion_id = #{opinionId}
                            getSqlWillCompilePart(builder, getSqlPart(group), obj);
                        }
                    }
                    if (val.equals("''")) {
                        if (map.get(key) != null && !(map.get(key).toString().length() == 0)) {
                            // 条件中的内容 例： and opinion_id = #{opinionId}
                            getSqlWillCompilePart(builder, getSqlPart(group), obj);
                        }
                    }
                } else {
                    Field field = obj.getClass().getDeclaredField(key);
                    field.setAccessible(true);
                    if (val.equals("null")) {
                        if (field.get(obj) != null) {
                            // 条件中的内容 例： and opinion_id = #{opinionId}
                            getSqlWillCompilePart(builder, getSqlPart(group), obj);
                        }
                    }
                    if (val.equals("''")) {
                        if (field.get(obj) != null && !(field.get(obj).toString().length() == 0)) {
                            // 条件中的内容 例： and opinion_id = #{opinionId}
                            getSqlWillCompilePart(builder, getSqlPart(group), obj);
                        }
                    }
                }
            }

            if (ifStr.contains("==")) {
                String[] split = ifStr.split("==");
                String key = split[0].trim();
                String val = split[1].trim();
                if (obj instanceof Map) {
                    Map map = (Map) obj;
                    if (val.equals("null")) {
                        if (map.get(key) == null) {
                            // 条件中的内容 例： and opinion_id = #{opinionId}
                            getSqlWillCompilePart(builder, getSqlPart(group), obj);
                        }
                    }
                    if (val.equals("''")) {
                        if (map.get(key) != null && map.get(key).toString().length() == 0) {
                            // 条件中的内容 例： and opinion_id = #{opinionId}
                            getSqlWillCompilePart(builder, getSqlPart(group), obj);
                        }
                    }
                } else {
                    Field field = obj.getClass().getDeclaredField(key);
                    field.setAccessible(true);
                    if (val.equals("null")) {
                        if (field.get(obj) == null) {
                            // 条件中的内容 例： and opinion_id = #{opinionId}
                            getSqlWillCompilePart(builder, getSqlPart(group), obj);
                        }
                    }
                    if (val.equals("''")) {
                        if (field.get(obj) != null && field.get(obj).toString().length() == 0) {
                            // 条件中的内容 例： and opinion_id = #{opinionId}
                            getSqlWillCompilePart(builder, getSqlPart(group), obj);
                        }
                    }
                }
            }
        }

//        return builder;
    }

    /**
     * 获取 条件中的内容 例： and opinion_id = #{opinionId}
     * @param group
     * @return
     */
    private String getSqlPart(String group) {
        return group.substring(group.indexOf(">") + 1, group.lastIndexOf("<"));
    }

    /**
     * 处理成最终sql
     * @param builder
     * @param sqlPart
     * @param obj
     * @throws Exception
     */
    private void getSqlWillCompilePart(StringBuilder builder, String sqlPart, Object obj) throws Exception {
        // 条件中的内容 例： and opinion_id = #{opinionId}

        String updateSql = sqlPart;
        // 匹配#{}表达式
        Pattern c = Pattern.compile("#\\{(\\w+)\\}");
        Matcher m1 = c.matcher(sqlPart);
        while (m1.find()) {
            String fieldName = m1.group(1);
//            System.out.println(fieldName);
            if (obj instanceof Map) {
                Map map = (Map) obj;
                Object o = map.get(fieldName);
                updateSql = updateSql.replaceAll("#\\{" + fieldName + "\\}", "?");
                params.add(o);
            } else {
                Field declaredField = obj.getClass().getDeclaredField(fieldName);
                declaredField.setAccessible(true);
                updateSql = updateSql.replaceAll("#\\{" + fieldName + "\\}", "?");
                params.add(declaredField.get(obj));
            }
        }
        builder.append(updateSql);
    }

}
